use QuanLyBongDa
go

---LOGIN
create proc [dbo].[login_BTC]
(
	@username varchar(20),
	@password varchar(50)
)
as
begin
	select * from BTC where Ma_BTC = @username and MKhau = @password
end

GO

--GET INFO
create proc [dbo].[getinfo_BTC]
	@MaBTC  varchar(20)
as
begin
	select *
	from BTC
	where Ma_BTC = @MaBTC
end
GO

create proc [dbo].[GetInfoHLV]
	@MaHLV varchar(20)
as
begin
	select *
	from HLV
	where Ma_HLV = @MaHLV
end
go

create proc [dbo].[GetInfoNTT]
	@MaNTT varchar(20)
as
begin
	select *
	from NTT
	where Ma_NTT = @MaNTT
end
go 

create proc [dbo].[GetInfoSAN]
	@MaSan varchar(20)
as
begin
	select *
	from SANVD
	where MaSan = @MaSan
end
go	

create proc [dbo].[GetCLBTheoMaBTC]
	@MaBTC  varchar(20)
as
begin
	select Ma_CLB,C.Ma_BTC,Ma_HLV,MaSan,Ma_NTT,Ten_CLB,SoCauThu,DPhuc
	from BTC B,CLB C
	where 	B.Ma_BTC = @MaBTC and B.Ma_BTC = C.Ma_BTC
end
GO 

--GET ID
create proc [dbo].[GetMaxID_CLB]
as
begin
select max(Ma_CLB)
FROM CLB
end

GO

--GET DANHSACHCLB
drop proc GetDS_CLB
create proc [dbo].[GetDS_CLB]

as
	select * from CLB 
go

--List HLV
create proc [dbo].[listHLV]
as
	select Ten_HLV
	from HLV
go

--List NTT
create proc [dbo].[listNTT]
as
	select Ten_NTT
	from NTT
go

--List SAN
create proc [dbo].[listSAN]
as
	select TenSan
	from SANVD
go

--SuaThongTinCLB
create proc [dbo].[SuaThongTinCLB]
(
	@Ma_CLB varchar(20),
	@Ma_BTC varchar(20),
	@Ma_HLV varchar(20),
	@MaSan varchar(20),
	@Ma_NTT varchar(20),
	@Ten_CLB nvarchar(20),
	@SoCauThu int,
	@DPhuc nvarchar(20)
)
as
begin
	update CLB set	Ma_BTC = @Ma_BTC,
					Ma_HLV = @Ma_HLV,
					MaSan = @MaSan,
					Ma_NTT = @Ma_NTT,
					Ten_CLB = @Ten_CLB,
					SoCauThu = @SoCauThu,
					DPhuc = @DPhuc
	where Ma_CLB = @Ma_CLB
end
go
--ThemHLV
create proc [dbo].[ThemHLV]
(
	@Ma_HLV varchar(20),
	@Ten_HLV nvarchar(20),
	@NgaySinh datetime,
	@Qtich nvarchar(20)
)
as
begin
	insert into HLV values ( @Ma_HLV,@Ten_HLV,@NgaySinh,@Qtich)
end
go
 
--SuaThongTinHLV
create proc [dbo].[SuaHLV]
(
	@Ma_HLV varchar(20),
	@Ten_HLV nvarchar(20),
	@NgaySinh datetime,
	@Qtich nvarchar(20)
)
as 
begin
	update HLV set	
					Ten_HLV = @Ten_HLV,
					NgaySinh = @NgaySinh,
					Qtich = @Qtich
				where Ma_HLV = @Ma_HLV
end
go

--XoaThongTinHLV
create proc [dbo].[XoaHLV]
	@Ma_HLV varchar(20)
as
begin
	delete from HLV where Ma_HLV = @Ma_HLV
end
go

--ThemVTCT
create proc [dbo].[ThemVTCT]
(
	@Ma_VTCT varchar(20),
	@Ten_VTCT nvarchar(60)
)
as
begin
	insert into VT_CT values ( @Ma_VTCT,@Ten_VTCT)
end
go

--XoaVTCC
create proc [dbo].[XoaVTCT]
(
	@Ma_VTCT varchar(20)
)
as
begin
	delete from VT_CT where Ma_VTCT = @Ma_VTCT
end
go

--SuaVTCT
create proc [dbo].[SuaVTCT]
(
	@Ma_VTCT varchar(20),
	@Ten_VTCT nvarchar(60)
)
as
begin update VT_CT set
						Ten_VTCT = @Ten_VTCT
					where Ma_VTCT = @Ma_VTCT
end
go

--ThemCauThu
create proc [dbo].[ThemCauThu]
(
	@Ma_CT varchar(20),
	@Ma_CLB varchar(20),
	@Ma_VTCT varchar(20),
	@Ten_CT nvarchar(60),
	@NgaySinh datetime,
	@Qtich nvarchar(20),
	@DiaChi nvarchar(60)
)
as
begin
	insert into CAUTHU values ( @Ma_CT, @Ma_CLB, @Ma_VTCT, @Ten_CT, @NgaySinh, @Qtich, @DiaChi)
end
go

--SuaCauThu
create proc [dbo].[SuaCauThu]
(
	@Ma_CT varchar(20),
	@Ma_CLB varchar(20),
	@Ma_VTCT varchar(20),
	@Ten_CT nvarchar(60),
	@NgaySinh datetime,
	@Qtich nvarchar(20),
	@DiaChi nvarchar(60)
)
as
begin
	update CAUTHU set
						Ma_CLB = @Ma_CLB,
						Ma_VTCT = @Ma_VTCT,
						Ten_CT = @Ten_CT,
						NgaySinh = @NgaySinh,
						DiaChi = @DiaChi
				where Ma_CT = @Ma_CT
end
go

--XoaCauThu
create proc [dbo].[XoaCauThu]
	@Ma_CT varchar(20)
as
begin
	delete from CAUTHU where Ma_CT = @Ma_CT
end
go
